package com.github.linmt.dbdoc4j.metadata.query.mysql;

import com.github.linmt.dbdoc4j.metadata.exception.QueryMetaDataException;
import com.github.linmt.dbdoc4j.metadata.model.mysql.MysqlColumnModel;
import com.github.linmt.dbdoc4j.metadata.model.mysql.MysqlTableModel;
import com.github.linmt.dbdoc4j.metadata.model.standard.ColumnModel;
import com.github.linmt.dbdoc4j.metadata.model.standard.TableModel;
import com.github.linmt.dbdoc4j.metadata.query.base.AbstractMetaDataQueryTemplate;
import com.github.linmt.dbdoc4j.utils.ResultSetUtils;
import org.apache.commons.lang3.StringUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * User: Linmt
 * Date: 2022/12/2
 * Time: 19:47
 * Description: Mysql元数据获取
 */
public class MysqlMetaDataQuery extends AbstractMetaDataQueryTemplate {

    public MysqlMetaDataQuery(DataSource dataSource) {
        super(dataSource);
    }

    @Override
    public List<TableModel> queryTableMetaData(String[] includeTablePatterns, String[] excludeTablePatterns) throws QueryMetaDataException {
        Connection connection = super.getConnection();
        try {
            List<MysqlTableModel> mysqlTableModels = this.getTables(connection, includeTablePatterns, excludeTablePatterns);
            List<MysqlColumnModel> mysqlColumnModels = this.getColumns(connection, includeTablePatterns, excludeTablePatterns);

            List<TableModel> tableModels = new ArrayList<>();
            Map<String, List<MysqlColumnModel>> groupColumns = mysqlColumnModels.stream().collect(Collectors.groupingBy(MysqlColumnModel::getTableName));

            for (MysqlTableModel mysqlTableModel : mysqlTableModels) {
                TableModel.Builder builder = TableModel.builder()
                        .tableName(mysqlTableModel.getTableName())
                        .tableType(mysqlTableModel.getTableType())
                        .remark(mysqlTableModel.getTableComment());

                builder.addAllColumn(
                        groupColumns.get(mysqlTableModel.getTableName()).stream().map(r -> {
                            ColumnModel columnModel = new ColumnModel();
                            columnModel.setColumnName(r.getColumnName());
                            columnModel.setRemark(r.getColumnComment());
                            columnModel.setColumnType(r.getDataType());
                            columnModel.setNullAble("YES".equals(r.getIsNullable()));
                            columnModel.setPrimaryKey("PRI".equals(r.getColumnKey()));
                            return columnModel;
                        }).collect(Collectors.toList())
                );

                tableModels.add(builder.build());
            }

            return tableModels;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            super.closeConnect();
        }

        return new ArrayList<>();
    }

    private List<MysqlTableModel> getTables(Connection connection, String[] includeTablePatterns, String[] excludeTablePatterns) {
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = connection.prepareStatement(getTableSql(includeTablePatterns, excludeTablePatterns));
            ps.setObject(1, super.getCatalog());
            setPreparedStatementParams(ps, includeTablePatterns, excludeTablePatterns);

            ResultSet resultSet = ps.executeQuery();
            List<MysqlTableModel> columnModels = ResultSetUtils.toObjectList(MysqlTableModel.class, resultSet);

            return columnModels;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                if (ps != null) {
                    try {
                        ps.close();
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                }
            }
        }

        return new ArrayList<>();
    }

    private List<MysqlColumnModel> getColumns(Connection connection, String[] includeTablePatterns, String[] excludeTablePatterns) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(getColumnSql(includeTablePatterns, excludeTablePatterns));
            ps.setObject(1, super.getCatalog());
            setPreparedStatementParams(ps, includeTablePatterns, excludeTablePatterns);

            rs = ps.executeQuery();
            List<MysqlColumnModel> columnModels = ResultSetUtils.toObjectList(MysqlColumnModel.class, rs);

            return columnModels;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                if (ps != null) {
                    try {
                        ps.close();
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                }
            }
        }

        return new ArrayList<>();
    }

    private void setPreparedStatementParams(PreparedStatement ps, String[] includeTablePatterns, String[] excludeTablePatterns) {
        int index = 2;
        if (includeTablePatterns != null) {
            for (String pattern : includeTablePatterns) {
                try {
                    ps.setString(index++, pattern.replaceAll("\\*", "%"));
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        if (excludeTablePatterns != null) {
            for (String pattern : excludeTablePatterns) {
                try {
                    ps.setString(index++, pattern.replaceAll("\\*", "%"));
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private String getTableSql(String[] includeTablePatterns, String[] excludeTablePatterns) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT TABLE_NAME,TABLE_TYPE,TABLE_COMMENT\n");
        sql.append("FROM information_schema.TABLES\n");
        sql.append("WHERE TABLE_SCHEMA = ?\n");
        sql.append(buildFilterSql(includeTablePatterns, excludeTablePatterns, "information_schema.TABLES"));
        sql.append("ORDER BY TABLE_NAME asc");

        return sql.toString();
    }

    private String getColumnSql(String[] includeTablePatterns, String[] excludeTablePatterns) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT table_name,column_name,column_default,is_nullable,data_type,column_key,column_comment\n");
        sql.append("FROM information_schema.columns\n");
        sql.append("where table_schema = ?\n");
        sql.append(buildFilterSql(includeTablePatterns, excludeTablePatterns, "information_schema.columns"));
        sql.append("order by TABLE_NAME asc,ORDINAL_POSITION asc");

        return sql.toString();
    }

    private String buildFilterSql(String[] includeTablePatterns, String[] excludeTablePatterns, String tableAlias) {
        StringBuilder filterWhereSql = new StringBuilder();

        // 拼接包含表的过滤语句sql，eg:
        // AND (TABLE_NAME like ? OR TABLE_NAME like ?)
        if (includeTablePatterns != null && includeTablePatterns.length > 0) {
            StringBuilder includeSql = new StringBuilder();
            for (int i = 0; i < includeTablePatterns.length; i++) {
                if (i != 0) includeSql.append(" OR ");

                includeSql.append(tableAlias);
                includeSql.append(".");
                includeSql.append("TABLE_NAME LIKE ? ");
            }
            filterWhereSql.append(" AND (");
            filterWhereSql.append(includeSql.toString());
            filterWhereSql.append(" )\n");
        }

        // 拼接排除表包含表的过滤语句sql，eg:
        // AND (TABLE_NAME not like ? AND TABLE_NAME NOT like ?)
        if (excludeTablePatterns != null && excludeTablePatterns.length > 0) {
            StringBuilder includeSql = new StringBuilder();
            for (int i = 0; i < excludeTablePatterns.length; i++) {
                if (i != 0) includeSql.append(" AND ");

                includeSql.append(tableAlias);
                includeSql.append(".");
                includeSql.append("TABLE_NAME not like ?");
            }
            filterWhereSql.append(" AND (");
            filterWhereSql.append(includeSql.toString());
            filterWhereSql.append(" )\n");
        }

        return filterWhereSql.toString();
    }
}
